package LawsonTransformJava.pgm;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;

import LawsonTransformJava.xref.XrefPlanCode;

import LawsonTransformJava.dao.DAOFactoryAccessDB;
import LawsonTransformJava.dao.DAOFactoryOracleDB;
import LawsonTransformJava.dao.DAOHRDepBen;
import LawsonTransformJava.dao.DAObject;
import LawsonTransformJava.data.TblHRDepBen;

/**
 * @author edavis
 *
 */
public class BN532 extends DAObject implements 
DAOHRDepBen, Runnable {

	private XrefPlanCode xPlanCodes = null;

	/**
	 * 
	 * @param output - Output file to write records
	 * @throws SQLException
	 */
	public BN532(File output) throws SQLException {
		// initialization 
		try {
			this.output = new FileWriter(this.outputFile = output);
			init(false);
		} catch (IOException e) {
			// Could no write properties file
			System.out.println("IOException: " + e);
		}
	}

	/**
	 * @param output 		Output file to write records
	 * @param allowBlanks	Allow Blanks if a Cross Reference field is not found.
	 * 						Only allowed for non-required fields.
	 * @throws SQLException
	 */
	public BN532(File output, boolean allowBlanks) throws SQLException {
		// initialization 
		try {
			this.output = new FileWriter(this.outputFile = output);
			init(allowBlanks);
		} catch (IOException e) {
			// Could no write properties file
			System.out.println("IOException: " + e);
		}
	}

	/**
	 * Initialization method that creates the data connection, cross reference DB connection and creates the
	 * appropriate Cross Reference mappings that are used in this Conversion Transformation Program.
	 * 
	 * @param allowBlanks - Allow Blanks if a Cross Reference field is not found.
	 * 						Only allowed for non-required fields.
	 * @throws IOException
	 * @throws SQLException
	 */
	private void init(boolean allowBlanks) throws IOException, SQLException {
		// Set the data and xref variables
		data = DAOFactoryOracleDB.getDAOOracleDB(true);
		xref = new DAOFactoryAccessDB();
		xPlanCodes = new XrefPlanCode();

		sqlData = "SELECT * FROM " + data.getSchema() + "." + TABLE_NAME;
		stmtData = data.getDataStatement();	    
		countStmtData = data.getCountStatement();
		//Set the Date Format for output
		df = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
	}

	public TblHRDepBen findBenefit() {
		TblHRDepBen ben = new TblHRDepBen();
		try {
			sqlData = "SELECT " + TABLE_NAME + ".* " +
			          " FROM " + data.getSchema() + "." + TABLE_NAME +
			          " " + (whereClause==null?"":whereClause);
			rslt = data.getDataStatement().executeQuery(sqlData);
			rsmd = rslt.getMetaData();
			if (rslt!=null && rslt.next()){
				hasMoreRecs = true;
				ben = (TblHRDepBen) createObject(rslt, ben);
			}
			else
				hasMoreRecs = false;
			return ben;
		} catch (SQLException e) {
			// Could not connect to the database
			System.out.println("SQLException: " + e);
		} catch (InvocationTargetException ite) {
			System.out.println("InvocationException: " + ite);
		} catch (IllegalAccessException iae) {
			System.out.println("IllegalAccessException: " + iae);
		}
		return null;
	}

	public TblHRDepBen findBenefit(String employeeNumber) {
		try {
			TblHRDepBen ben = new TblHRDepBen();
			whereClause = "WHERE " + TABLE_NAME + ".EMPLOYEE=" + employeeNumber;
			sqlData = "SELECT " + TABLE_NAME + ".* " +
			          " FROM " + data.getSchema() + "." + TABLE_NAME +
			          " " + (whereClause==null?"":whereClause);
			rslt = data.getDataStatement().executeQuery(sqlData);
			rsmd = rslt.getMetaData();
			rslt.next();
			ben = (TblHRDepBen) createObject(rslt, ben);
			return ben;
		} catch (SQLException e) {
			// Could not connect to the database
			System.out.println("SQLException: " + e);
		} catch (InvocationTargetException ite) {
			System.out.println("InvocationException: " + ite);
		} catch (IllegalAccessException iae) {
			System.out.println("IllegalAccessException: " + iae);
		}
		return new TblHRDepBen();
	}

	public TblHRDepBen nextBenefit() {
		TblHRDepBen ben = new TblHRDepBen();
		try {
			if (rslt.next()){
				hasMoreRecs = true;
				ben = (TblHRDepBen) createObject(rslt, ben);
			}
			else
				hasMoreRecs = false;
			return ben;
		} catch (SQLException e) {
			// Could not connect to the database
			System.out.println("SQLException: " + e);
		} catch (InvocationTargetException ite) {
			System.out.println("InvocationException: " + ite);
		} catch (IllegalAccessException iae) {
			System.out.println("IllegalAccessException: " + iae);
		}
		return null;
	}

	public boolean hasMore () {
		return hasMoreRecs;
	}

	public int countBenefits() {
		try {
			countRecs = data.getCountStatement().executeQuery("SELECT COUNT(*) FROM " + data.getSchema() + "." + TABLE_NAME);
			countRecs.next();
			return recCount=countRecs.getInt(1);
		} catch (SQLException e) {
			// Could not connect to the database
			System.out.println("SQLException: " + e);
		}
		return 0;
	}

	public void run() {
		TblHRDepBen ben = null;

		try {
			//Update current date/time
			Date now = new Date();
			System.out.println(outputFile.getName() + " Start " + df.format(now));
			//Write timestamp to output file
			output.write(outputFile.getName() + " Start " + df.format(now) + "\n");

			ben = findBenefit();
			while (hasMore()) {
				output.write(writeBN532Layout(ben) + "\n");
				output.flush();
				ben = nextBenefit();
			}
			//Update current date/time
			now = new Date();
			System.out.println(outputFile.getName() + " Finish " + df.format(now));
			//Write timestamp to output file
			output.write(outputFile.getName() + " Finish " + df.format(now) + "\n");
			output.close();
		} catch (IOException e) {
			// Could not write properties file
			// Could not create output file
			System.out.println("IOException: " + e);
		}

	}

	//Output the HR511 format
	public String writeBN532Layout(TblHRDepBen ben) {

		return (
				"A" + "," +
				"1" + "," +
				ben.getEmployee() + "," +
				ben.getDependent() + "," +
				ben.getPlan_type() + "," +
				// @ TODO Add Plan Code xlate when defined.
//				xPlanCodes.xlate(ben.getPlan_code()) + "," +
				ben.getPlan_code() + "," +
				ben.getEmp_start() + "," +
				ben.getStart_date() + "," +
				ben.getStop_date() + "," +
				ben.getCreation_date() + "," +
				ben.getUpd_date() + "," +
				ben.getTime_stamp() + "," +
				ben.getUser_id() + "," +
				"N," +							//If blank, defaults from plan (Y/N) - N
				"\"HISTORY\"," +				//Reason for enrollment
				"1"								//MemberId: 1=SSN;2=EE#						
				);
	}
}